class NoteSqlMap(object):
    notice_list = "select t1.content,t1.notice_id,t1.title,t1.notice_time,t1.is_top,t1.url,t2.type_name,t3.status_name,t5.real_name,t6.dept_name from aoa_notice_list as t1 inner join aoa_type_list as t2 on t1.type_id=t2.type_id inner join aoa_status_list as t3 on t1.status_id=t3.status_id inner join aoa_notice_user_relation as t4 on t1.notice_id=t4.relatin_notice_id inner join aoa_user as t5 on t1.user_id=t5.user_id left join aoa_dept as t6 on t5.dept_id=t6.dept_id where t4.relatin_user_id={user_id}"
    notice_check = "select t1.notice_id,t1.title,t1.content,t1.notice_time,t2.real_name,t3.dept_name,t1.receiver_user as receiver_real from aoa_notice_list as t1 inner join aoa_user as t2 on t1.user_id=t2.user_id inner join aoa_dept as t3 on t2.dept_id=t3.dept_id where notice_id={notice_id}"
    notice_read = "select count(distinct relatin_user_id) as read_num from aoa_notice_user_relation where is_read=1 and relatin_notice_id={notice_id}"
    NOTICE_READ_CHECK = "select relatin_id,is_read from aoa_notice_user_relation where relatin_notice_id={notice_id} and relatin_user_id={user_id}"
    CREATE_READ_RECORD = "insert into aoa_notice_user_relation(is_read,relatin_notice_id,relatin_user_id) values(1,{notice_id},{user_id})"
    UPDATE_READ_RECORD = "update aoa_notice_user_relation set is_read=1 where relatin_user_id={user_id} and relatin_notice_id={notice_id}"
    FIND_NAME = "SELECT user_name,real_name FROM aoa_user WHERE user_id={user_id}"
    INSERT_NOTICE = "INSERT INTO aoa_notice_list(content, is_top, modify_time, notice_time, status_id, title, type_id, url, user_id, receiver, receiver_user) VALUES('{content}', {is_top}, now(), now(), {status_id}, '{title}', {type_id}, '{url}', {user_id}, '{user_name}', '{real_name}')"
    INSERT_NOTICE_NO = "INSERT INTO aoa_notice_list(content, is_top, modify_time, notice_time, status_id, title, type_id, user_id, receiver, receiver_user) VALUES('{content}', {is_top}, now(), now(), {status_id}, '{title}', {type_id}, {user_id}, '{user_name}', '{real_name}')"
    GET_ID = "select @@IDENTITY as id"
    INSERT_RELATION = "INSERT INTO aoa_notice_user_relation(is_read, relatin_notice_id, relatin_user_id) VALUES(0, {notice_id}, {relatin_user_id})"

    FILTER_PARAMS = {
        "query_str": "t1.title like '%{query_str}%'"
    }
